"""Convert user details to jsonb and move user profile image url into details column

Revision ID: fd4fc850d7ea
Revises: 89bc7873a3e0
Create Date: 2022-01-31 15:24:16.507888

"""
from alembic import op
import sqlalchemy as sa
from sqlalchemy.dialects.postgresql import JSON, JSONB

from redash.models import db

# revision identifiers, used by Alembic.
revision = 'fd4fc850d7ea'
down_revision = '89bc7873a3e0'
branch_labels = None
depends_on = None


def upgrade():
    connection = op.get_bind()

    ### commands auto generated by Alembic - please adjust! ###
    op.alter_column('users', 'details',
               existing_type=JSON(astext_type=sa.Text()),
               type_=JSONB(astext_type=sa.Text()),
               existing_nullable=True,
               existing_server_default=sa.text("'{}'::jsonb"))
    ### end Alembic commands ###

    update_query = """
    update users
    set details = details::jsonb || ('{"profile_image_url": "' || profile_image_url || '"}')::jsonb
    where 1=1
    """
    connection.execute(update_query)
    op.drop_column("users", "profile_image_url")


def downgrade():
    # ### commands auto generated by Alembic - please adjust! ###
    connection = op.get_bind()
    op.add_column("users", sa.Column("profile_image_url", db.String(320), nullable=True))

    update_query = """
    update users set
    profile_image_url = details->>'profile_image_url',
    details = details - 'profile_image_url' ;
    """

    connection.execute(update_query)
    db.session.commit()
    op.alter_column('users', 'details',
               existing_type=JSONB(astext_type=sa.Text()),
               type_=JSON(astext_type=sa.Text()),
               existing_nullable=True,
               existing_server_default=sa.text("'{}'::json"))

    # ### end Alembic commands ###
